package com.hnkywl.sts.dao.xsgl;

import com.hnkywl.sts.dao.base.RegionDao;
import com.hnkywl.sts.entity.base.Sjzdz;
import com.hnkywl.sts.entity.cggl.Glfy;
import com.hnkywl.sts.entity.xsgl.Kh;
import com.hnkywl.sts.entity.xsgl.Xsd;
import com.hnkywl.sts.entity.xsgl.Xsmx;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.criterion.*;
import org.hibernate.type.Type;
import org.springframework.stereotype.Repository;

import java.text.ParseException;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 * User: Administrator
 * Date: 13-7-6
 * Time: 下午4:36
 * To change this template use File | Settings | File Templates.
 */

@Repository
public class NbxsdDao extends SimpleHibernateTemplate<Xsd, Long> {
    public Page<Xsd> findXsdList(String xsdh, Date fhsjStart, Date fhsjEnd, Long xshtId, Long khId,Long khbmId, Long gcId, Long xsgsId, Long xsbmId, Long xsrId, String zt, String sftzd, int pageNo, int pageSize) throws ParseException {
        Page<Xsd> page = new Page<Xsd>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("kh", "kh", CriteriaSpecification.LEFT_JOIN)
                .createAlias("khbm", "khbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("gc", "gc", CriteriaSpecification.LEFT_JOIN)
                .createAlias("thck", "thck", CriteriaSpecification.LEFT_JOIN)
                .createAlias("fyzr", "fyzr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsgs", "xsgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsbm", "xsbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsr", "xsr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbgs", "sbgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbbm", "sbbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbr", "sbr", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("xssl").as("xssl"))
                        .add(Property.forName("xsje").as("xsje"))
                        .add(Property.forName("glfyje").as("glfyje"))
                        .add(Property.forName("xsbhsje").as("xsbhsje"))
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("xsht.id").as("xsht_id"))
                        .add(Projections.sqlProjection("xsdh as xsdh", new String[]{"xsdh"}, new Type[]{Hibernate.STRING}))
                        .add(Projections.sqlProjection("xslx as xslx", new String[]{"xslx"}, new Type[]{Hibernate.STRING}))
                        .add(Projections.sqlProjection("fhsj as fhsj", new String[]{"fhsj"}, new Type[]{Hibernate.DATE}))
                        .add(Projections.sqlProjection("this_.date_created as sxsj", new String[]{"sxsj"}, new Type[]{Hibernate.DATE}))
                        .add(Projections.sqlProjection("sftzd as sftzd", new String[]{"sftzd"}, new Type[]{Hibernate.BOOLEAN}))
                        .add(Property.forName("kh.qc").as("kh_qc"))
                        .add(Property.forName("kh.jc").as("kh_jc"))
                        .add(Property.forName("khbm.qc").as("khbm_qc"))
                        .add(Property.forName("khbm.jc").as("khbm_jc"))
                        .add(Property.forName("gc.mc").as("gc_mc"))
                        .add(Property.forName("thck.mc").as("thck_mc"))
                        .add(Property.forName("fyzr.z").as("fyzr_z"))
                        .add(Property.forName("dsyfje").as("dsyfje"))
                        .add(Property.forName("xsgs.qc").as("xsgs_qc"))
                        .add(Property.forName("xsgs.jc").as("xsgs_jc"))
                        .add(Property.forName("xsbm.qc").as("xsbm_qc"))
                        .add(Property.forName("xsbm.jc").as("xsbm_jc"))
                        .add(Property.forName("xsr.xm").as("xsr_xm"))
                        .add(Property.forName("sbgs.jc").as("sbgs_jc"))
                        .add(Property.forName("sbbm.jc").as("sbbm_jc"))
                        .add(Property.forName("sbr.xm").as("sbr_xm"))
                        .add(Projections.sqlProjection("this_.zt as zt", new String[]{"zt"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("dateCreated"))
                )
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (StringUtils.isNotEmpty(xsdh)) {
            criteria.add(Property.forName("xsdh").like(xsdh, MatchMode.START));
        }
        if (fhsjStart != null) {
            criteria.add(Restrictions.ge("dateCreated", sDaysAfterNowDate(fhsjStart)));
        } else {
            Date start = sDaysAfterNowDate(new Date());
            criteria.add(Restrictions.ge("dateCreated", start));
        }
        if (fhsjEnd != null) {
            criteria.add(Restrictions.le("dateCreated", jDaysAfterNowDate(fhsjEnd)));
        } else {
            Date end = jDaysAfterNowDate(new Date());
            criteria.add(Restrictions.le("dateCreated", end));
        }
        if (StringUtils.isNotEmpty(zt)) {
            if (zt.equals("ybc")) {
                criteria.add(Restrictions.eq("zt", "ybc"));
                criteria.add(Restrictions.or(Restrictions.isNull("yck"),Restrictions.eq("yck", false)));
                criteria.add(Restrictions.or(Restrictions.isNull("yjs"),Restrictions.eq("yjs", false)));
            } else if (zt.equals("yck")) {
                criteria.add(Restrictions.eq("yck", true));
            } else if (zt.equals("yjs")) {
                criteria.add(Restrictions.eq("yjs", true));
            }
        }
        if (xshtId != null) {
            criteria.add(Restrictions.eq("xsht.id", xshtId));
        }
        if (xsgsId != null) {
            criteria.add(Restrictions.eq("xsgs.id", xsgsId));
        }
        if (xsbmId != null) {
            criteria.add(Restrictions.eq("xsbm.id", xsbmId));
        }
        if (xsrId != null) {
            criteria.add(Restrictions.eq("xsr.id", xsrId));
        }
        if (khId != null) {
            criteria.add(Restrictions.eq("kh.id", khId));
        }
        if (khbmId != null) {
            criteria.add(Restrictions.eq("khbm.id", khbmId));
        }
        if (sftzd != null) {
            criteria.add(Restrictions.eq("sftzd", false));
        } else {
            //criteria.add(Restrictions.isNull("xsjsd"));
            criteria.add(Restrictions.eq("zt", "ysp"));
            criteria.add(Restrictions.or(Restrictions.isNull("yjs"), Restrictions.eq("yjs", false)));
        }

        RegionDao.createDataRegion(criteria);
        criteria.add(Restrictions.eq("xslx", "nbxs"));
        criteria.addOrder(Property.forName("dateCreated").desc());
        criteria.addOrder(Property.forName("id").desc());
        criteria.addOrder(Property.forName("yxsd.id").desc());


        Page list = this.findByCriteria(page, criteria);
        return list;
    }

    //   当前日期加减n天后的日期，返回String   (yyyy-mm-dd)
    public Date jDaysAfterNowDate(Date date) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.setTime(date);
        rightNow.set(java.util.Calendar.HOUR_OF_DAY, 23);
        rightNow.set(java.util.Calendar.MINUTE, 59);
        rightNow.set(java.util.Calendar.SECOND, 59);
        return rightNow.getTime();
    }

    //   当前日期加减n天后的日期，返回String   (yyyy-mm-dd)
    public Date sDaysAfterNowDate(Date date) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.setTime(date);
        rightNow.set(java.util.Calendar.HOUR_OF_DAY, 0);
        rightNow.set(java.util.Calendar.MINUTE, 0);
        rightNow.set(java.util.Calendar.SECOND, 0);
        return rightNow.getTime();
    }

    public List<Map> findXsdmxSearch(Long xsjsdId) {
        Criteria criteria = getSession().createCriteria(Xsmx.class)
                .createAlias("kh", "kh", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jtwz", "wzgg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("kh.cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jzfs", "jzfs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsd", "xsd", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("xsd.id").as("xsd.id"))
                        .add(Property.forName("xsd.xsdh").as("xsdh"))
                        .add(Property.forName("kh.id").as("kh.id"))
                        .add(Property.forName("xscb").as("xscb"))
                        .add(Property.forName("wzgg.id").as("wzgg.id"))
                        .add(Property.forName("wzgg.code").as("wzgg.code"))
                        .add(Property.forName("wzgg.name").as("wzgg.name"))
                        .add(Property.forName("wzgg.ggxh").as("wzgg.ggxh"))
                        .add(Property.forName("wzgg.dw").as("wzgg.dw"))
                        .add(Property.forName("js").as("js"))
                        .add(Property.forName("sbsl").as("sbsl"))
                        .add(Property.forName("jzfs.id").as("jzfs.id"))
                        .add(Property.forName("jzfs.z").as("jzfs.z"))
                        .add(Property.forName("ck_js").as("ck_js"))
                        .add(Property.forName("cd.id").as("cd.id"))
                        .add(Property.forName("cd.z").as("cd.z"))
                        .add(Property.forName("ck_sbsl").as("ck_sbsl"))
                        .add(Property.forName("hsj").as("hsj"))
                        .add(Property.forName("bhsj").as("bhsj"))
                        .add(Property.forName("jsbhse").as("je"))
                        .add(Property.forName("jsse").as("se"))
                ).add(Restrictions.eq("xsjsd.id", xsjsdId))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        return criteria.list();
    }

    public List<Map> findXsmxList(Long xsdId) {
        Criteria criteria = getSession().createCriteria(Xsmx.class)
                .createAlias("kh", "kh", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jtwz", "wzgg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("kh.cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jzfs", "jzfs", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("kh.id").as("kh.id"))
                        .add(Property.forName("kh.khbm").as("kh.khbm"))
                        .add(Property.forName("kh.kkdsl").as("kkdsl"))
                        .add(Property.forName("wzgg.id").as("wzgg.id"))
                        .add(Property.forName("wzgg.code").as("wzgg.code"))
                        .add(Property.forName("wzgg.name").as("wzgg.name"))
                        .add(Property.forName("wzgg.ggxh").as("wzgg.ggxh"))
                        .add(Property.forName("wzgg.dw").as("wzgg.dw"))
                        .add(Property.forName("cd.id").as("cd.id"))
                        .add(Property.forName("cd.z").as("cd.z"))
                        .add(Property.forName("xscb").as("xscb"))
                        .add(Property.forName("js").as("js"))
                        .add(Property.forName("sbsl").as("sbsl"))
                        .add(Property.forName("jzfs.id").as("jzfs.id"))
                        .add(Property.forName("jzfs.z").as("jzfs.z"))
                        .add(Property.forName("hsj").as("hsj"))
                        .add(Property.forName("bhsj").as("bhsj"))
                        .add(Property.forName("jsbhse").as("je"))
                        .add(Property.forName("jsse").as("se"))
                        .add(Property.forName("bz").as("bz"))
                ).add(Restrictions.eq("xsd.id", xsdId))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        return (List<Map>) criteria.list();
    }

    public List<Map> findXsmxJsList(Long xsdId) {
        Criteria criteria = getSession().createCriteria(Xsmx.class)
                .createAlias("kh", "kh", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jtwz", "wzgg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("kh.cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jzfs", "jzfs", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("kh.id").as("kh.id"))
                        .add(Property.forName("kh.khbm").as("kh.khbm"))
                        .add(Property.forName("kh.kkdsl").as("kkdsl"))
                        .add(Property.forName("wzgg.id").as("wzgg.id"))
                        .add(Property.forName("wzgg.code").as("wzgg.code"))
                        .add(Property.forName("wzgg.name").as("wzgg.name"))
                        .add(Property.forName("wzgg.ggxh").as("wzgg.ggxh"))
                        .add(Property.forName("wzgg.dw").as("wzgg.dw"))
                        .add(Property.forName("cd.id").as("cd.id"))
                        .add(Property.forName("cd.z").as("cd.z"))
                        .add(Property.forName("xscb").as("xscb"))
                        .add(Property.forName("js").as("js"))
                        .add(Property.forName("sbsl").as("sbsl"))
                        .add(Property.forName("jzfs.id").as("jzfs.id"))
                        .add(Property.forName("jzfs.z").as("jzfs.z"))
                        .add(Property.forName("hsj").as("hsj"))
                        .add(Property.forName("bhsj").as("bhsj"))
                        .add(Property.forName("jsbhse").as("je"))
                        .add(Property.forName("jsse").as("se"))
                        .add(Property.forName("bz").as("bz"))
                ).add(Restrictions.eq("xsd.id", xsdId))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        return (List<Map>) criteria.list();
    }

    public List<Map> findXsfyList(Long xsdId) {
        Criteria criteria = getSession().createCriteria(Glfy.class)
                .createAlias("sydw", "sydw", CriteriaSpecification.LEFT_JOIN)
                .createAlias("fylb", "fylb", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("sydw.id").as("sydw.id"))
                        .add(Property.forName("sydw.qc").as("sydw.qc"))
                        .add(Property.forName("fylb.id").as("fylb.id"))
                        .add(Property.forName("fylb.fykmjc").as("fylb.fykmjc"))
                        .add(Property.forName("jfsl").as("jfsl"))
                        .add(Property.forName("dj").as("dj"))
                        .add(Property.forName("je").as("je"))
                        .add(Property.forName("bz").as("bz"))
                ).add(Restrictions.eq("xsd.id", xsdId))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        return (List<Map>) criteria.list();
    }

    public List<Map> findXsmxByKh(Kh kh) {
        Criteria criteria = getSession().createCriteria(Xsmx.class)
                .createAlias("kh", "kh", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsd", "xsd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("kh.jtwz", "wzgg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("kh.cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jzfs", "jzfs", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("kh.khbm").as("kh.khbm"))
                        .add(Property.forName("kh.kkdsl").as("kkdsl"))
                        .add(Property.forName("xsd.xsdh").as("xsd.xsdh"))
                        .add(Property.forName("wzgg.id").as("wzgg.id"))
                        .add(Property.forName("wzgg.code").as("wzgg.code"))
                        .add(Property.forName("wzgg.name").as("wzgg.name"))
                        .add(Property.forName("wzgg.ggxh").as("wzgg.ggxh"))
                        .add(Property.forName("wzgg.dw").as("wzgg.dw"))
                        .add(Property.forName("cd.id").as("cd.id"))
                        .add(Property.forName("cd.z").as("cd.z"))
                        .add(Property.forName("xscb").as("xscb"))
                        .add(Property.forName("js").as("js"))
                        .add(Property.forName("sbsl").as("sbsl"))
                        .add(Property.forName("ck_js").as("ck_js"))
                        .add(Property.forName("ck_sbsl").as("ck_sbsl"))
                        .add(Property.forName("jzfs.id").as("jzfs.id"))
                        .add(Property.forName("jzfs.z").as("jzfs.z"))
                        .add(Property.forName("hsj").as("hsj"))
                        .add(Property.forName("bhsj").as("bhsj"))
                        .add(Property.forName("jsbhse").as("je"))
                        .add(Property.forName("jsse").as("se"))
                        .add(Property.forName("bz").as("bz"))
                ).add(Restrictions.eq("kh.id", kh.getId()))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        return (List<Map>) criteria.list();
    }

    public void deleteXsmx(Long xsdId) {
        String hql = "update Xsmx set deleted=1 where xsd.id=?";
        Query query = this.getSession().createQuery(hql);
        query.setLong(0, xsdId);
        query.executeUpdate();
    }

    /**
     * 条件查询销售单
     *
     * @param xsdh
     * @param fhsjStart
     * @param fhsjEnd
     * @param xsjsdId
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Page<Xsd> findXsdSearch(String xsdh, Date fhsjStart, Date fhsjEnd, Long xsjsdId, Long xsgsId, Long khId, int pageNo, int pageSize) {
        Page<Xsd> page = new Page<Xsd>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("dateCreated");

        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("kh", "kh", CriteriaSpecification.LEFT_JOIN)
                .createAlias("gc", "gc", CriteriaSpecification.LEFT_JOIN)
                .createAlias("thck", "thck", CriteriaSpecification.LEFT_JOIN)
                .createAlias("fyzr", "fyzr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsgs", "xsgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsbm", "xsbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsr", "xsr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbgs", "sbgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbbm", "sbbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbr", "sbr", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("xssl").as("xssl"))
                        .add(Property.forName("xsje").as("xsje"))
                        .add(Property.forName("xsbhsje").as("xsbhsje"))
                        .add(Property.forName("glfyje").as("glfyje"))
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("xsht.id").as("xsht_id"))
                        .add(Projections.sqlProjection("xsdh as xsdh", new String[]{"xsdh"}, new Type[]{Hibernate.STRING}))
                        .add(Projections.sqlProjection("fhsj as fhsj", new String[]{"fhsj"}, new Type[]{Hibernate.DATE}))
                        .add(Property.forName("kh.qc").as("kh_qc"))
                        .add(Property.forName("kh.id").as("kh_id"))
                        .add(Property.forName("jhdz").as("jhdz"))
                        .add(Property.forName("gc.mc").as("gc_mc"))
                        .add(Property.forName("thck.mc").as("thck_mc"))
                        .add(Property.forName("fyzr.z").as("fyzr_z"))
                        .add(Property.forName("dsyfje").as("dsyfje"))
                        .add(Property.forName("xsgs.qc").as("xsgs_qc"))
                        .add(Property.forName("xsgs.jc").as("xsgs_jc"))
                        .add(Property.forName("sbgs.jc").as("sbgs_jc"))
                        .add(Property.forName("sbbm.jc").as("sbbm_jc"))
                        .add(Property.forName("sbr.xm").as("sbr_xm"))
                        .add(Projections.sqlProjection("this_.zt as zt", new String[]{"zt"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("dateCreated"))
                )
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (StringUtils.isNotEmpty(xsdh)) {
            criteria.add(Property.forName("xsdh").like(xsdh, MatchMode.START));
        }
        if (fhsjStart != null) {
            criteria.add(Restrictions.gt("fhsj", fhsjStart));
        }
        if (fhsjEnd != null) {
            criteria.add(Restrictions.lt("fhsj", fhsjEnd));
        }
        if (khId != null) {
            criteria.add(Restrictions.eq("kh.id", khId));
        }
        if (xsgsId != null) {
            criteria.add(Restrictions.eq("sbgs.id", xsgsId));
        }
        if (xsjsdId != null) {
            criteria.add(Restrictions.eq("xsjsd.id", xsjsdId));
        } else {
            DetachedCriteria dc = DetachedCriteria.forClass(Xsmx.class);
            dc.setProjection(Projections.property("xsd.id"));
            dc.add(Restrictions.isNull("xsjsd"));
            criteria.add(Subqueries.propertyIn("id", dc));
//            criteria.add(Restrictions.eq("zt", "ysp"));
        }
        criteria.add(Restrictions.eq("sftzd", false));
        RegionDao.createDataRegion(criteria);
        Page list = this.findByCriteria(page, criteria);
        return list;
    }

    public Page<Xsd> findXsdXspsList(String psdh, Sjzdz fyzr, int pageNo, int pageSize) {
        Page<Xsd> page = new Page<Xsd>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("dateCreated");

        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("kh", "kh", CriteriaSpecification.LEFT_JOIN)
                .createAlias("gc", "gc", CriteriaSpecification.LEFT_JOIN)
                .createAlias("thck", "thck", CriteriaSpecification.LEFT_JOIN)
                .createAlias("fyzr", "fyzr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsgs", "xsgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsbm", "xsbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsr", "xsr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("psd", "psd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("psd.wls", "wls", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("xssl").as("xssl"))
                        .add(Property.forName("xsje").as("xsje"))
                        .add(Property.forName("xsbhsje").as("xsbhsje"))
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("xsht.id").as("xsht_id"))
                        .add(Projections.sqlProjection("xsdh as xsdh", new String[]{"xsdh"}, new Type[]{Hibernate.STRING}))
                        .add(Projections.sqlProjection("fhsj as fhsj", new String[]{"fhsj"}, new Type[]{Hibernate.DATE}))
                        .add(Property.forName("psd.psdh").as("psdh"))
                        .add(Property.forName("psd.id").as("psd_id"))
                        .add(Property.forName("psd.psfy").as("psfy"))
                        .add(Property.forName("wls.qc").as("wls_qc"))
                        .add(Property.forName("psd.shch").as("shch"))
                        .add(Property.forName("psd.jsy").as("jsy"))
                        .add(Property.forName("psd.lxdh").as("lxdh"))
                        .add(Property.forName("kh.qc").as("kh_qc"))
                        .add(Property.forName("kh.id").as("kh_id"))
                        .add(Property.forName("jhdz").as("jhdz"))
                        .add(Property.forName("gc.mc").as("gc_mc"))
                        .add(Property.forName("thck.mc").as("thck_mc"))
                        .add(Property.forName("fyzr.z").as("fyzr_z"))
                        .add(Property.forName("dsyfje").as("dsyfje"))
                        .add(Property.forName("xsgs.qc").as("xsgs_qc"))
                        .add(Property.forName("xsbm.qc").as("xsbm_qc"))
                        .add(Property.forName("xsr.xm").as("xsr_xm"))
                        .add(Projections.sqlProjection("this_.zt as zt", new String[]{"zt"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("dateCreated"))
                )
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (StringUtils.isNotEmpty(psdh)) {
            criteria.add(Property.forName("psd.psdh").like(psdh, MatchMode.START));
        }
        criteria.add(Restrictions.eq("fyzr", fyzr));
        criteria.add(Restrictions.or(Restrictions.eq("zt", "ysp"), Restrictions.eq("zt", "yjs")));
        criteria.add(Restrictions.eq("sftzd", false));
        RegionDao.createDataRegion(criteria);
        Page list = this.findByCriteria(page, criteria);
        return list;
    }

    /**
     * 根据日期查询销售单数
     *
     * @param sdate
     * @return
     */
    public List<Xsd> findXsdCountByDate(String sdate) {
        String hql = "select count(*) from Xsd where to_char(dateCreated, 'yyyy-mm-dd')=? " + RegionDao.createDataRegion("and");
        return this.find(hql, sdate);
    }

    /**
     * 查询业务收入
     *
     * @param sdate
     * @param htType
     * @return
     */
    public List findYwsr(String sdate, String htType) {
        String ywsrSql = "";
        if (null != htType && htType.equals("bgxs")) {
            ywsrSql = " select nvl(sum(tm.sbsl*tm.bhsj),0) as sumje  from t_biz_jsd tj,t_biz_xsmx tm,t_biz_xsd ts " +
                    " where tj.id =tm.xsjsd_id and tm.xsd_id = ts.id and ts.deleted=0 and ts.yjs= 1 and  to_char(ts.date_created, 'yyyy-mm') = ? and ts.xslx = ? ";
        } else {
            ywsrSql = " select nvl(sum(tm.sbsl*tm.bhsj),0) as sumje  from t_biz_jsd tj,t_biz_xsmx tm,t_biz_xsd ts " +
                    " where tj.id =tm.xsjsd_id and tm.xsd_id = ts.id and ts.deleted=0 and ts.yjs= 1 and to_char(ts.date_created, 'yyyy-mm') = ? and ts.xslx != ? ";
        }
        return this.findBySQL(ywsrSql, sdate, "bgxs");
    }

    public List<Map> findKhByXsd(Long gsjgId) {
        String hql = "select distinct kh.id as id,kh.jc as jc, qyfr.nsh as qyfr_nsh from Xsd xsd left join xsd.kh kh left join kh.qyfr qyfr where xsd.sbgs.id=?";
        Query query = this.getSession().createQuery(hql);
        query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        query.setLong(0, gsjgId);
        return query.list();
    }

    /**
     * 首页显示销售单数
     *
     * @param zt
     * @param sftzd
     * @return
     */
    public List findXsdList(String zt, Integer sftzd) {
        String joinTableSql = " left join t_sys_yg yg on yg.id=t.createuser_id ";
        String dataRegion = RegionDao.createSQLDataRegion(joinTableSql, "yg");
        String whereSql = " where t.deleted ='0' and t.zt=? and t.sftzd=? ";
        String sql = "select t.zt from t_biz_xsd t " +
                dataRegion + whereSql;
        return this.findBySQL(sql, zt, sftzd);
    }
}



